#Imports
from flask import Flask
from flask import render_template
from flask import request
from flask import g
from flask import session
from flask import redirect
from flask import url_for
import sqlite3

app = Flask(__name__)
app.secret_key = "secret :P"

#Database stuff
DATABASE = 'db'
def connect_db():
    return sqlite3.connect(DATABASE)
@app.before_request
def before_request():
    g.db = connect_db()
@app.after_request
def after_request(response):
    g.db.close()
    return response


#Index handler
@app.route("/",methods=["GET","POST"])
def index():
	return redirect(url_for("threads"))

#Register handler
@app.route("/register",methods=["GET","POST"])
def register():
	message = ""
	if request.method == "POST":
		g.db.execute("INSERT INTO users (name,password) VALUES (?,?)",[request.form["user"],request.form["password"]])
		g.db.commit()
		message = "User created successfully!"
	return render_template ("register.html",message=message)

#Login handler
@app.route("/login",methods=["POST"])
def login():
	message = ""
	if request.method == "POST":
		userData = g.db.execute("SELECT id, password FROM users WHERE name = ?",[request.form["user"]])
		data = userData.fetchone()
		if data is None:
			message = "Invalid user"
		else:
			password = data[1]
			if password == request.form["password"]:
				session["username"] = request.form["user"]
				session["userid"] = data[0]
				message = "Login successfull!"
			else:
				message = "Incorrect password"
	return redirect(url_for("index"))

#Logout handler
@app.route("/logout",methods=["GET","POST"])
def logout():
	session.pop('username', None)
	return redirect(url_for("index"))
	
#Threads handler
@app.route("/threads",methods=["GET","POST"])
def threads():
	if request.method == "POST":
		g.db.execute("INSERT INTO threads (title,last_post,created_by) VALUES (?,CURRENT_TIMESTAMP,?)",[request.form["title"],session["userid"]])
		g.db.commit()
		idList = g.db.execute("SELECT id FROM threads WHERE created_by = ? AND title = ? ORDER BY created DESC LIMIT 1",[session["userid"],request.form["title"]])
		id = idList.fetchone()[0]
		g.db.execute("INSERT INTO posts (thread_id,message,created_by) VALUES (?,?,?)",[id,request.form["text"],session["userid"]])
		g.db.commit()
	threadList = g.db.execute("SELECT id, title FROM threads")
	threads = [dict(id=row[0], title=row[1]) for row in threadList.fetchall()]
	return render_template ("threads.html",threads=threads)

#Posts handler
@app.route("/threads/<int:thread_id>",methods=["GET","POST"])
def post(thread_id):
	if request.method == "POST":
		g.db.execute("INSERT INTO posts (thread_id,message,created_by) VALUES (?,?,?)",[thread_id,request.form["text"],session["userid"]])
		g.db.commit()
	postsList = g.db.execute("SELECT p.message, p.created, u.name FROM posts p JOIN users u ON p.created_by = u.id WHERE thread_id = ? ORDER BY created ASC",[thread_id])
	posts = [dict(message=row[0], date=row[1], user=row[2]) for row in postsList.fetchall()]
	return render_template ("posts.html",posts=posts,thread_id=thread_id)

#User Handler
@app.route("/user/<int:user_id>",methods=["GET"])
def user(user_id):
	userList = g.db.execute("SELECT name FROM users WHERE id = ?",[user_id])
	user = [dict(name=row[0]) for row in userList.fetchall()]
	return render_template("user.html",user=user)

#Main program
if __name__ == "__main__":
	app.run(host="127.0.0.1", port=8080, debug=True)